Release 10.1A: OpenEdge Data Management:
SQL Reference

INSTR

Searches character string char_expression1 for the character string char_expression2. The search begins at start_pos of char_expression1. If occurrence is specified, then INSTR searches for the nth occurrence, where n is the value of the fourth argument.

The position (with respect to the start of char_expression1) is returned if a search is successful. Zero is returned if no match can be found.

Syntax

INSTR ( char_expression1 , char_expression2 [ , start_pos [ , occurrence ] ] )

Example

This example illustrates the INSTR function:

SELECT cust_no, last_name FROM customer WHERE INSTR (LOWER (addr), 'heritage') > 0 ;

Notes

The first and second arguments must be CHARACTER data type.
The third and fourth arguments, if specified, must be SMALLINT or TINYINT data type.
The value for start position in a character string is the ordinal number of the character in the string. The very first character in a string is at position 1, the second character is at position 2, the nth character is at position n.
If you do not specify start_pos, a default value of 1 is assumed.
If you do not specify occurrence, a default value of 1 is assumed.
The result is INTEGER data type.
If any of the argument expressions evaluate to NULL, the result is NULL.
A char_expression and the result can contain multi-byte characters.

Compatibility

Progress extension